package com.cortex.pineapple.database;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

import org.json.JSONException;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;

import com.cortex.pineapple.fetch.RequestData.SOURCE_TYPE;
import com.cortex.pineapple.utilities.MessagesLog;
import com.cortex.pineapple.utilities.ParseJson;
import com.cortex.pineapple.utilities.Utilities;
import com.cortext.pineapple.link.Link;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.os.AsyncTask;
import android.util.Log;

public class DataBase {

	private SQLiteDatabase db;
	private DbHelper dbHelper;

	// table season query search name
	private final static  String TABLE_SEASON_QUERY_SEARCH = DbHelper.TABLE_SEASON_QUERY_SEARCH;
	// season query search all columns
	private final static String[] ALL_COLUMNS_SEASON = {
		DbHelper.COL_SEASON_ID,
		DbHelper.COL_SEASON_NAME,
		DbHelper.COL_SEASON_QUERY,
		DbHelper.COL_SEASON_VISIT_LINKS_ID,
		DbHelper.COL_SEASON_START_DATE};
	// parameters of season query search
	private final static String COL_SEASON_ID = DbHelper.COL_SEASON_ID;
	private final static String COL_SEASON_NAME = DbHelper.COL_SEASON_NAME;
	private final static String COL_SEASON_QUERY = DbHelper.COL_SEASON_QUERY;
	private final static String COL_SEASON_VISIT_LINKS_ID = DbHelper.COL_SEASON_VISIT_LINKS_ID;
	private final static String COL_SEASON_START_DATE = DbHelper.COL_SEASON_START_DATE;

	// table visit links name
	private final static  String TABLE_VISIT_LINKS = DbHelper.TABLE_VISIT_LINKS;
	// visit links all columns
	private final static String[] ALL_COLUMNS_VISIT_LINKS = {
		DbHelper.COL_VISIT_LINK_ID,
		DbHelper.COL_VISIT_LINK_SEASON_ID,
		DbHelper.COL_VISIT_LINK_SOURCE,
		DbHelper.COL_VISIT_LINK_TITLE,
		DbHelper.COL_VISIT_LINK_DESCRIPTION,
		DbHelper.COL_VISIT_LINK_URL,
		DbHelper.COL_VISIT_LINK_START_DATE,
		DbHelper.COL_VISIT_LINK_FAVORITE,
		DbHelper.COL_VISIT_LINK_FAVICON,
		DbHelper.COL_VISIT_LINK_SECTIONS_SELECTED};

	private final static String COL_VISIT_LINK_ID = DbHelper.COL_VISIT_LINK_ID;
	private final static String COL_VISIT_RELATED_SEASON_ID = DbHelper.COL_VISIT_LINK_SEASON_ID;
	private final static String COL_VISIT_LINK_SOURCE = DbHelper.COL_VISIT_LINK_SOURCE;
	private final static String COL_VISIT_LINK_TITLE = DbHelper.COL_VISIT_LINK_TITLE;
	private final static String COL_VISIT_LINK_DESCRIPTION = DbHelper.COL_VISIT_LINK_DESCRIPTION;
	private final static String COL_VISIT_LINK_URL = DbHelper.COL_VISIT_LINK_URL;
	public final static String COL_VISIT_LINK_START_DATE = DbHelper.COL_VISIT_LINK_START_DATE;
	public final static String COL_VISIT_LINK_FAVORITE = DbHelper.COL_VISIT_LINK_FAVORITE;
	public final static String COL_VISIT_LINK_FAVICON = DbHelper.COL_VISIT_LINK_FAVICON;
	public final static String COL_VISIT_LINK_SECTIONS_SELECTED = DbHelper.COL_VISIT_LINK_SECTIONS_SELECTED;


	public DataBase(Context context){
		dbHelper = new DbHelper(context);
	}
	public boolean isOpen(){
		return db != null && db.isOpen();
	}
	private void open() throws SQLiteException {
		if(!isOpen()){
			db = dbHelper.getWritableDatabase();
		}
	}
	private void close(){
		dbHelper.close();
	}

	public void doAction(ActionOnDataBase action){
		new StartActionThread(action).execute();
	}

	private class StartActionThread extends AsyncTask<Void, Void, List<SeasonQuerySearch>>{

		private ActionOnDataBase actionDataBase;
		private OnDataBaseFinishedReadListener listener;
		String message = "";
		ACTION action;

		public StartActionThread(ActionOnDataBase actionDataBase){
			this.actionDataBase = actionDataBase;
			action = actionDataBase.getAction();
			listener = actionDataBase.getListener();
		}
		@Override
		protected List<SeasonQuerySearch> doInBackground(Void... params) {
			Object data = actionDataBase.getData();
			List<SeasonQuerySearch> seasons = new ArrayList<SeasonQuerySearch>();
			if(action == ACTION.INSERT_SEASON || action == ACTION.INSERT_VISIT_LINK || action == ACTION.DELETE_SEASON){

				if(!(data instanceof SeasonQuerySearch) && 
						!(data instanceof VisitLink)){
					cancel(true);
					return null;
				}
			}
			switch(actionDataBase.getAction()){
			case DELETE_SEASON:
				deleteSeason((SeasonQuerySearch) data);
				seasons.add((SeasonQuerySearch)data);
				break;
			case INSERT_SEASON:
				insertSeason((SeasonQuerySearch) data);
				seasons.add((SeasonQuerySearch)data);
				break;
			case INSERT_VISIT_LINK:
				insertVisitLink((VisitLink) data);
				// add read seasons by id
				break;
			case READ_ALL_SEASONS:
				seasons.addAll(readAllSeasons());
				break;
			case DELETE_ALL_SEASONS:
				deleteAll();
				break;
			case READ_NEXT_SEASONS:
				seasons.addAll(readNextSeasons(actionDataBase.getTop(), actionDataBase.getLimit()));
				break;
			case READ_PREVIOUS_SEASONS:
				seasons.addAll(readPreviousSeasons(actionDataBase.getTop(), actionDataBase.getLimit()));
				break;
			case READ_LAST_SEASONS:
				seasons.addAll(readLastSeasons(actionDataBase.getLimit()));
				break;
			case READ_SEASONS_BY_QUERY:
				seasons.addAll(readSeasonsByQuery(actionDataBase.getQuery()));
				break;
			case UPDATE_SEASON:
				updateSeason((SeasonQuerySearch) data);
			case UPDATE_VISIT_LINK:
				updateVisitLink((VisitLink) data);
				break;
			default:
				break;
			}
			return seasons;
		}
		@Override
		protected void onPreExecute() {
			if(listener != null && listener instanceof OnDataBaseActionStateChangedListener){
				OnDataBaseActionStateChangedListener l = (OnDataBaseActionStateChangedListener)listener;
				l.startAction(action);
			}
		}

		@Override
		protected void onPostExecute(List<SeasonQuerySearch> seasons) {
			if(listener != null){
				listener.finishedAction(seasons);
			}
		}
		@Override
		protected void onCancelled() {
			if(listener != null && listener instanceof OnDataBaseActionStateChangedListener){
				OnDataBaseActionStateChangedListener l = (OnDataBaseActionStateChangedListener)listener;
				l.cancelledAction(message);
			}
		}



		private boolean insertSeason(SeasonQuerySearch seasonQuery){
			boolean retValue = true;
			try{
				open();
				ContentValues cv = new ContentValues();
				cv.put(COL_SEASON_NAME, seasonQuery.getName());
				cv.put(COL_SEASON_QUERY, seasonQuery.getQuery());
				List<String> ids = new ArrayList<String>();
				for (VisitLink link : seasonQuery.getVisitLinks()) {
					ids.add("" + link.getId());
				}
				cv.put(COL_SEASON_VISIT_LINKS_ID, Utilities.list2csv(ids));
				cv.put(COL_SEASON_START_DATE, seasonQuery.getStartSeason().getTime());
				seasonQuery.setId(db.insert(TABLE_SEASON_QUERY_SEARCH, null, cv));
			}
			catch(SQLiteException e){
				Log.d(MessagesLog.APPLICATION_EXCEPTION,"throw exception when insert row into data base message:" + e.getMessage());
				retValue = false;
			}
			finally{
				close();
			}
			return retValue;
		}
		private boolean updateSeason(SeasonQuerySearch seasonQuery){
			boolean retValue = true;
			try{
				open();
				ContentValues cv = new ContentValues();
				cv.put(COL_SEASON_NAME, seasonQuery.getName());
				cv.put(COL_SEASON_QUERY, seasonQuery.getQuery());
				List<String> ids = new ArrayList<String>();
				for (VisitLink link : seasonQuery.getVisitLinks()) {
					ids.add("" + link.getId());
				}
				cv.put(COL_SEASON_VISIT_LINKS_ID, Utilities.list2csv(ids));
				cv.put(COL_SEASON_START_DATE, seasonQuery.getStartSeason().getTime());
				db.update(TABLE_SEASON_QUERY_SEARCH, cv, seasonQuery.getId() + " = ?",
						new String[] { seasonQuery.getId() + ""});
			}
			catch(SQLiteException e){
				Log.e(MessagesLog.APPLICATION_EXCEPTION,"throw exception when insert row into data base message:" + e.getMessage());
				retValue = false;
			}
			finally{
				close();
			}
			return retValue;
		}

		private boolean deleteSeason(SeasonQuerySearch seasonQuery)
		{
			boolean retValue = true;
			open();
			try{
				db.delete(TABLE_SEASON_QUERY_SEARCH, COL_SEASON_ID + "='" + seasonQuery.getId() + "'", null);
				db.delete(TABLE_VISIT_LINKS, COL_VISIT_RELATED_SEASON_ID + "='" + seasonQuery.getId() + "'", null);
			}
			catch(Exception e){
				Log.e(MessagesLog.APPLICATION_EXCEPTION,"throw exception when delete row from data base message:" + e.getMessage());			
				retValue = false;
			}
			finally{
				close();
			}
			return retValue;
		}
		public void deleteAll() {
			open();
			try{
				db.delete(TABLE_SEASON_QUERY_SEARCH,null,null);
			}finally{
				close();
			}
		}
		private List<SeasonQuerySearch> readAllSeasons(){
			open();
			try{
				Cursor c = db.query(TABLE_SEASON_QUERY_SEARCH, ALL_COLUMNS_SEASON, null, null, null, null, COL_SEASON_START_DATE + "desc",null);
				return readSeasons(c);
			}catch (Exception e) {
				Log.e(MessagesLog.APPLICATION_EXCEPTION,"throw exception read seasons from dataBase message:" + e.getMessage());			
			}
			finally{
				close();
			}
			return new ArrayList<SeasonQuerySearch>();
		}   
		private List<SeasonQuerySearch> readSeasonsByQuery(String query){
			open();
			try{
				String where = COL_SEASON_QUERY + " like ?";
				Cursor c = db.query(TABLE_SEASON_QUERY_SEARCH, ALL_COLUMNS_SEASON, where, new String[] {"%" + query + "%"}, null, null,  COL_SEASON_START_DATE + "desc",null);
				return readSeasons(c);
			}catch (Exception e) {
				Log.e(MessagesLog.APPLICATION_EXCEPTION,"throw exception read seasons from dataBase message:" + e.getMessage());			
			}
			finally{
				close();
			}
			return new ArrayList<SeasonQuerySearch>();
		} 
		private List<SeasonQuerySearch> readNextSeasons(int top ,int limit){
			open();
			try{
				String where = COL_SEASON_ID + " < ? AND" +  COL_SEASON_ID + " > ?";
				Cursor c = db.query(TABLE_SEASON_QUERY_SEARCH, ALL_COLUMNS_SEASON, where, new String[] {"" + top,"" + (top-limit)}, null, null,  COL_SEASON_START_DATE + "desc","");
				return readSeasons(c);
			}catch (Exception e) {
				Log.e(MessagesLog.APPLICATION_EXCEPTION,"throw exception read seasons from dataBase message:" + e.getMessage());			
			}
			finally{
				close();
			}
			return new ArrayList<SeasonQuerySearch>();
		} 
		private List<SeasonQuerySearch> readPreviousSeasons(int top ,int limit){
			open();
			try{
				String where = COL_SEASON_ID + " > ? AND" +  COL_SEASON_ID + " < ?";
				Cursor c = db.query(TABLE_SEASON_QUERY_SEARCH, ALL_COLUMNS_SEASON, where, new String[] {"" + top,"" + (top+limit)}, null, null,  COL_SEASON_START_DATE + "desc","" + limit);
				return readSeasons(c);
			}catch (Exception e) {
				Log.e(MessagesLog.APPLICATION_EXCEPTION,"throw exception read seasons from dataBase message:" + e.getMessage());			
			}
			finally{
				close();
			}
			return new ArrayList<SeasonQuerySearch>();
		} 
		private List<SeasonQuerySearch> readLastSeasons(int limit){
			open();
			try{
				String where = COL_SEASON_ID + " > ? ";
				Cursor c = db.query(TABLE_SEASON_QUERY_SEARCH, ALL_COLUMNS_SEASON, where, new String[] {"" + 0}, null, null,  COL_SEASON_START_DATE + "desc","" + limit);
				return readSeasons(c);
			}catch (Exception e) {
				Log.e(MessagesLog.APPLICATION_EXCEPTION,"throw exception read seasons from dataBase message:" + e.getMessage());			
			}
			finally{
				close();
			}
			return new ArrayList<SeasonQuerySearch>();
		} 

		private List<SeasonQuerySearch> readSeasons(Cursor c) throws Exception{
			List<SeasonQuerySearch> list = new ArrayList<SeasonQuerySearch>();
			c.moveToFirst();
			if(c.isFirst()){
				do
				{
					SeasonQuerySearch season = new SeasonQuerySearch();

					season.setId(c.getLong((0)));

					season.setName(c.getString(1));
					season.setQuery(c.getString(2));
					season.setStartSeason(new Date(c.getLong(4)));
					season.setVisitLinks(readVisitLinksBySeasonId(season.getId()));
					list.add(season);

				}while(c.moveToNext());
			}
			c.close();
			return list;
		}
		private List<VisitLink> readVisitLinksBySeasonId(long id){
			List<VisitLink> list = new ArrayList<VisitLink>();
			open();
			try{
				String where = COL_VISIT_RELATED_SEASON_ID + " = " + id;
				Cursor c = db.query(TABLE_VISIT_LINKS, ALL_COLUMNS_VISIT_LINKS, where, null, null, null, COL_VISIT_LINK_START_DATE + "desc",null);

				if (c.moveToFirst()) {
					do {
						VisitLink visitLink = new VisitLink();
						visitLink.setId(c.getLong(0));
						visitLink.setIdSeason(c.getLong(1));
						visitLink.setSource(SOURCE_TYPE.values()[c.getInt(2)]);	
						visitLink.setTitle(c.getString(3));
						visitLink.setDescription(c.getString(4));	
						visitLink.setUrl(c.getString(5));	
						visitLink.setStartVisit(new Date(c.getLong(6)));
						visitLink.setFavorite(c.getInt(7) == 1?true:false);

						byte[] blob = c.getBlob(8);
						if(blob != null){
							Bitmap b=BitmapFactory.decodeByteArray(blob, 0, blob.length);
							visitLink.setFavicon(b);
						}
						visitLink.setSectionsSelected(Utilities.csv2ArrayListImprove(c.getString(9)));

						list.add(visitLink);
					} while (c.moveToNext());
				}
			}catch (Exception e) {
				Log.e(MessagesLog.APPLICATION_EXCEPTION,"throw exception read visitLinks from dataBase message:" + e.getMessage());			
			}
			finally{
				close();
			}
			return list;
		}  

		private boolean insertVisitLink(VisitLink visitLink){
			boolean retValue = true;

			try{
				open();
				ContentValues cv = visitLink2CV(visitLink);
				visitLink.setId(db.insert(TABLE_VISIT_LINKS, null, cv));
			}
			catch(SQLiteException e){
				Log.e(MessagesLog.APPLICATION_EXCEPTION,"throw exception when insert row into data base message:" + e.getMessage());
				retValue = false;
			}
			finally{
				close();
			}
			return retValue;
		}
		private boolean updateVisitLink(VisitLink visitLink){
			boolean retValue = true;

			try{
				open();
				ContentValues cv = visitLink2CV(visitLink);
				int success = db.update(TABLE_VISIT_LINKS, cv, COL_VISIT_LINK_ID + " = ?",
						new String[] { visitLink.getId() + ""});
				if(success <= 0){
					visitLink.setId(db.insert(TABLE_VISIT_LINKS, null, cv));
				}
			}
			catch(SQLiteException e){
				Log.e(MessagesLog.APPLICATION_EXCEPTION,"throw exception when insert row into data base message:" + e.getMessage());
				retValue = false;
			}
			finally{
				close();
			}
			return retValue;
		}
		private ContentValues visitLink2CV(VisitLink visitLink){
			ContentValues cv = new ContentValues();
			cv.put(COL_VISIT_RELATED_SEASON_ID, visitLink.getIdSeason());
			cv.put(COL_VISIT_LINK_SOURCE, visitLink.getSource().ordinal());
			cv.put(COL_VISIT_LINK_TITLE, visitLink.getTitle());
			cv.put(COL_VISIT_LINK_DESCRIPTION, visitLink.getDescription());
			cv.put(COL_VISIT_LINK_URL, visitLink.getUrl());
			cv.put(COL_VISIT_RELATED_SEASON_ID, visitLink.getIdSeason());
			cv.put(COL_VISIT_LINK_START_DATE, visitLink.getStartVisit().getTime());
			cv.put(COL_VISIT_LINK_FAVORITE, visitLink.isFavorite()?1:0);
			
			if(visitLink.getFavicon() != null){
				byte[] blob;
				Bitmap b = visitLink.getFavicon();
				ByteArrayOutputStream bos=new ByteArrayOutputStream();
				b.compress(Bitmap.CompressFormat.PNG, 100, bos);
				blob=bos.toByteArray();
				cv.put(COL_VISIT_LINK_FAVICON, blob);
			}
			cv.put(COL_VISIT_LINK_SECTIONS_SELECTED, Utilities.list2csvImprove(visitLink.getSectionsSelected()));
			return cv;
		}
	}
	public interface OnDataBaseActionStateChangedListener extends OnDataBaseFinishedReadListener{
		void startAction(ACTION action);
		void cancelledAction(String message);
	}
	public interface OnDataBaseFinishedReadListener{
		void finishedAction(List<SeasonQuerySearch> seasons);
	}
	public enum ACTION{
		INSERT_SEASON,
		INSERT_VISIT_LINK,
		UPDATE_SEASON,
		UPDATE_VISIT_LINK,
		DELETE_SEASON,
		DELETE_ALL_SEASONS,
		READ_ALL_SEASONS,
		READ_NEXT_SEASONS,
		READ_PREVIOUS_SEASONS,
		READ_SEASONS_BY_QUERY,
		READ_LAST_SEASONS
	}
}
